﻿<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="ibatisnet" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <statements>
    <select id="TaskLogic_GetMyTask" resultClass="Workflow.Entity.TaskEntity" >
      SELECT TASKID,
      a.PROCESSNAME,
      a.INCIDENT,
      b.SUMMARY,
      b.INITIATOR,
      a.STEPLABEL,
      a.TASKUSER,
      a.ASSIGNEDTOUSER,
      a.STATUS,
      a.SUBSTATUS,
      a.STARTTIME,
      a.ENDTIME,
      a.OVERDUETIME
      ,b.STATUS as PROCESSSTATUS,
      b.APLICANT ,
      b.DEPARTMENT
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      WHERE a.STATUS=1 AND a.ASSIGNEDTOUSER=$ASSIGNEDTOUSER$
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
      ORDER BY $Sort$
    </select>
    <select id="TaskLogic_GetMyTaskCount"  >
      SELECT count(1) FROM V_TASKS a INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      WHERE a.STATUS=1 AND a.ASSIGNEDTOUSER=$ASSIGNEDTOUSER$
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
    </select>

    <select id="TaskLogic_GetMyRequest" resultClass="Workflow.Entity.TaskEntity" >
      SELECT TASKID,
      a.PROCESSNAME,
      a.INCIDENT,
      b.SUMMARY,
      b.INITIATOR,
      a.STEPLABEL,
      a.TASKUSER,
      a.ASSIGNEDTOUSER,
      a.STATUS,
      a.SUBSTATUS,
      a.STARTTIME,
      a.ENDTIME,
      a.OVERDUETIME
      ,b.STATUS as PROCESSSTATUS，
      b.APLICANT ,
      b.DEPARTMENT
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      INNER JOIN V_ULT_PROCESSSTEPS c  ON a.PROCESSNAME=c.PROCESSNAME and a.PROCESSVERSION=c.PROCESSVERSION and a.STEPID=c.STEPID AND c.STEPTYPE=2
      LEFT JOIN wf_applicant d ON trim(a.PROCESSNAME) = trim(d.PROCESSNAME) AND a.INCIDENT = d.INCIDENT
      WHERE 1=1 AND (ASSIGNEDTOUSER=$ASSIGNEDTOUSER$ OR d.APPLICANT=$ASSIGNEDTOUSER$)
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
      ORDER BY $Sort$
    </select>
    <select id="TaskLogic_GetMyRequestCount"  >
      SELECT count(1)
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      INNER JOIN V_ULT_PROCESSSTEPS c  ON a.PROCESSNAME=c.PROCESSNAME and a.PROCESSVERSION=c.PROCESSVERSION and a.STEPID=c.STEPID AND c.STEPTYPE=2
      LEFT JOIN wf_applicant d ON trim(a.PROCESSNAME) = trim(d.PROCESSNAME) AND a.INCIDENT = d.INCIDENT
      WHERE 1=1 AND (ASSIGNEDTOUSER=$ASSIGNEDTOUSER$ OR d.APPLICANT=$ASSIGNEDTOUSER$)
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
    </select>

    <select id="TaskLogic_GetMyApproval" resultClass="Workflow.Entity.TaskEntity">
      SELECT TASKID,
      a.PROCESSNAME,
      a.INCIDENT,
      b.SUMMARY,
      b.INITIATOR,
      a.STEPLABEL,
      a.TASKUSER,
      a.ASSIGNEDTOUSER,
      a.STATUS,
      a.SUBSTATUS,
      a.STARTTIME,
      a.ENDTIME,
      a.OVERDUETIME
      ,b.STATUS as PROCESSSTATUS，
      b.APLICANT,
      b.DEPARTMENT
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      INNER JOIN V_ULT_PROCESSSTEPS c  ON a.PROCESSNAME=c.PROCESSNAME and a.PROCESSVERSION=c.PROCESSVERSION and a.STEPID=c.STEPID
      WHERE c.STEPTYPE=4 AND a.STATUS in (3,4,7) AND ASSIGNEDTOUSER=$ASSIGNEDTOUSER$
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
      ORDER BY $Sort$
    </select>
    <select id="TaskLogic_GetMyApprovalCount">
      SELECT COUNT(1)
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      INNER JOIN V_ULT_PROCESSSTEPS c  ON a.PROCESSNAME=c.PROCESSNAME and a.PROCESSVERSION=c.PROCESSVERSION and a.STEPID=c.STEPID
      WHERE c.STEPTYPE=4 AND a.STATUS in (3,4,7) AND ASSIGNEDTOUSER=$ASSIGNEDTOUSER$
      <dynamic prepend="AND">
        <isParameterPresent>
          a.STARTTIME BETWEEN #STARTTIME# AND #ENDTIME#
        </isParameterPresent>
      </dynamic>
      $filter$
    </select>

    <select id="TaskLogic_GetEntity"  resultClass="Workflow.Entity.TaskEntity" >
      SELECT TASKID,
      a.PROCESSNAME,
      a.INCIDENT,
      '' as SUMMARY,
      a.STEPLABEL,
      a.TASKUSER,
      a.ASSIGNEDTOUSER,
      a.STATUS,
      a.SUBSTATUS,
      a.STARTTIME,
      a.ENDTIME,
      a.OVERDUETIME,
      ' ' APLICANT,
      ' ' DEPARTMENT
      FROM V_TASKS a
      WHERE taskID=#taskID#
    </select>

    <select id="TaskLogic_GetStartEntity"  resultClass="Workflow.Entity.TaskEntity" >
      SELECT INITIATEID as TASKID
      ,PROCESSNAME
      ,0 as INCIDENT
      ,'' as SUMMARY
      ,STEPLABEL
      ,'' as TASKUSER
      ,'' as ASSIGNEDTOUSER
      ,0 as STATUS
      ,0 as SUBSTAUTS
      FROM INITIATE
      WHERE INITIATEID=#taskID#
    </select>

    <insert id="TaskLogic_InsertBackFromArchive">
      INSERT INTO INCIDENTS SELECT * FROM $ArchiveDBName$..INCIDENTS WHERE PROCESSNAME=#processName# AND INCIDENT=#incident#
      INSERT INTO TASKS SELECT * FROM $ArchiveDBName$..TASKS WHERE PROCESSNAME=#processName# AND INCIDENT = #incident#
      DELETE FROM $ArchiveDBName$..INCIDENTS WHERE PROCESSNAME=#processName# AND INCIDENT=#incident#
      DELETE FROM $ArchiveDBName$..TASKS WHERE PROCESSNAME=#processName# AND INCIDENT = #incident#
    </insert>

    <insert id="TaskLogic_InsertBackFromArchiveOracle">
      begin
      INSERT INTO INCIDENTS SELECT * FROM $ArchiveDBName$.INCIDENTS WHERE PROCESSNAME=#processName# AND INCIDENT=#incident#;
      INSERT INTO TASKS SELECT * FROM $ArchiveDBName$.TASKS WHERE PROCESSNAME=#processName# AND INCIDENT = #incident#;
      DELETE FROM $ArchiveDBName$.INCIDENTS WHERE PROCESSNAME=#processName# AND INCIDENT=#incident#;
      DELETE FROM $ArchiveDBName$.TASKS WHERE PROCESSNAME=#processName# AND INCIDENT = #incident#;
      end;
    </insert>

    <select id="TaskLogic_GetTaskList" resultClass="Workflow.Entity.TaskEntity" >
      SELECT TASKID,
      a.PROCESSNAME,
      a.INCIDENT,
      b.SUMMARY,
      b.INITIATOR,
      a.STEPID,
      a.STEPLABEL,
      a.TASKUSER,
      a.ASSIGNEDTOUSER,
      a.STATUS,
      a.SUBSTATUS,
      a.STARTTIME,
      a.ENDTIME,
      b.STATUS as PROCESSSTATUS,
      a.OVERDUETIME
      FROM V_TASKS a
      INNER JOIN V_INCIDENTS b  ON a.PROCESSNAME = b.PROCESSNAME AND a.INCIDENT = b.INCIDENT
      WHERE a.PROCESSNAME=#PROCESSNAME# AND a.INCIDENT=#INCIDENT#
    </select>

    <select id="TaskLogic_GetStepType"  >
      SELECT     STEPTYPE
      FROM         V_ULT_PROCESSSTEPS
      WHERE STEPID=#STEPID#
    </select>
  </statements>
</sqlMap>